package com.huweilong.tingshu.repository;

import com.huweilong.tingshu.data_object.TrackDO;
import com.huweilong.tingshu.util.DBUtil;
import com.huweilong.tingshu.util.Log;
import com.huweilong.tingshu.view_object.TrackVO;
import com.huweilong.tingshu.view_object.publish.PlayTrack;
import lombok.SneakyThrows;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @author 是阿秋啊
 * @date 2022/07/20 21:17
 **/
public class TrackRepo {
    @SneakyThrows
    public void insert(Integer uid, String title, String type, InputStream contentInputStream) {
        String sql = "insert into tracks (uid,title,type,content) values(?,?,?,?)";
        try(Connection c = DBUtil.connection()){
            try(PreparedStatement ps = c.prepareStatement(sql)){
                ps.setInt(1,uid);
                ps.setString(2,title);
                ps.setString(3,type);
                // 数据库表中的longblob就是一种二进制流 <-> BinaryStream
                ps.setBinaryStream(4,contentInputStream);

                Log.println("执行SQL:"+ps.toString());
                ps.executeUpdate();
            }
        }
    }

    @SneakyThrows
    public List<TrackDO> selectListByUidLimitOffset(Integer uid,int limit, int offset) {
        // content字段较大，一次查太多，性能会下降
        List<TrackDO> list = new ArrayList<>();
        String sql = "select tid,uid,title,type from tracks where uid = ? order by tid desc limit ? offset ?";
        try(Connection c = DBUtil.connection()) {
            try(PreparedStatement ps = c.prepareStatement(sql)) {
                ps.setInt(1,uid);
                ps.setInt(2,limit);
                ps.setInt(3,offset);
                Log.println("执行SQL" + sql);
                try(ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        TrackDO trackDO = new TrackDO(
                                rs.getInt("tid"),
                                rs.getInt("uid"),
                                rs.getString("title"),
                                rs.getString("type")
                        );
                        list.add(trackDO);
                    }
                }
            }
        }
        return list;
    }

    @SneakyThrows
    public int selectCountByUid(Integer uid) {
        String sql = "select count(*) from tracks where uid = ?";
        try(Connection c = DBUtil.connection()){
            try(PreparedStatement ps = c.prepareStatement(sql)){
                ps.setInt(1,uid);

                Log.println("执行SQL："+sql);
                try(ResultSet rs = ps.executeQuery()){
                    rs.next();
                    return rs.getInt(1);
                }
            }
        }
    }

    @SneakyThrows
    public List<TrackDO> selectListByTidList(List<Integer> tidList) {
        List<TrackDO> trackDOList = new ArrayList<>();
        String s = String.join(", ", tidList.stream().map(String::valueOf).collect(Collectors.toList()));
        String sql = String.format("select tid,uid,title,type from tracks where tid in (%s) order by tid",s);
        try(Connection c = DBUtil.connection()){
            try(PreparedStatement ps = c.prepareStatement(sql)){
                Log.println("执行SQL:"+sql);
                try(ResultSet rs = ps.executeQuery()){
                    while (rs.next()){
                        trackDOList.add(new TrackDO(
                                rs.getInt("tid"),
                                rs.getInt("uid"),
                                rs.getString("title"),
                                rs.getString("type")
                        ));
                    }
                    return trackDOList;
                }
            }
        }
    }

    @SneakyThrows
    public List<TrackVO> selectCandidateTrackByUidAndNotCand(Integer uid, String aid) {
        List<TrackVO> list = new ArrayList<>();
        String sql = "select tid,title from tracks where uid = ? and tid not in (select tid from relations where aid = ?)";

        try(Connection c = DBUtil.connection()){
            try(PreparedStatement ps = c.prepareStatement(sql)){
                ps.setInt(1,uid);
                ps.setString(2,aid);
                Log.println("执行SQL:"+ps);
                try(ResultSet rs = ps.executeQuery()){
                    while (rs.next()) {
                        list.add(new TrackVO(
                                rs.getInt("tid"),
                                rs.getString("title")
                        ));
                    }
                    return list;
                }
            }
        }
    }

    @SneakyThrows
    public PlayTrack selectTarckByTid(String tid) {
        String sql = "select type,content from tracks where tid = ?";
        try(Connection c = DBUtil.connection()) {
            try(PreparedStatement ps = c.prepareStatement(sql)){
                ps.setString(1,tid);
                Log.println("执行SQL:"+ps);
                try(ResultSet rs =ps.executeQuery()){
                    if (!rs.next()){
                        throw new RuntimeException("查不到对应tid音频");
                    }
                    return new PlayTrack(
                            rs.getString("type"),
                            rs.getBinaryStream("content")
                    );
                }
            }
        }
    }
}